package com.totoro.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/*
 * Excel数据导入到Mysql的工具类
 * 
 * @param filePath
 * 
 * @param value
 * 
 * @return
 */
public class ExcelToMysql {

	// 记录类的输出信息
	static Log log = LogFactory.getLog(ExcelToMysql.class);

	public static List<String[]> excelToList(String filePath, String sheetStr) {
		List<String[]> list = new ArrayList<String[]>();

		try {
			// 创建对Excel工作簿文件的引用
			HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(
					filePath));
			// 在Excel文档中，第一张工作表的缺省索引是0
			// 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);
			HSSFSheet sheet = wookbook.getSheet(sheetStr);
			// 获取到Excel文件中的所有行数
			int rows = sheet.getPhysicalNumberOfRows();
			// 获取到Excel文件中的所有的列
			int cells = sheet.getRow(0).getPhysicalNumberOfCells();
			System.out.println("列数：" + cells);
			// 遍历行
			for (int i = 1; i < rows; i++) {
				// 读取左上端单元格
				HSSFRow row = sheet.getRow(i);
				// 行不为空
				if (row != null) {
					String value = "";
					// 遍历列
					for (int j = 0; j < cells; j++) {
						// 获取到列的值
						HSSFCell cell = row.getCell(j);
						if (cell != null) {
							switch (cell.getCellType()) {
							case HSSFCell.CELL_TYPE_FORMULA:
								break;
							case HSSFCell.CELL_TYPE_NUMERIC:
								value += cell.getNumericCellValue() + ",";
								break;
							case HSSFCell.CELL_TYPE_STRING:
								value += cell.getStringCellValue() + ",";
								break;
							default:
								//原为value +=  ",";祁超修改
								value += null + ",";
								break;
							}
						} else {
							value +=  null + ",";
						}
					}
					//将excel行数据封装为数组

					String[] val = value.split(",");
					//将excel数据封装为List
					list.add(val);
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		// 返回excel中数据,封装为列表
		return list;
	}
}
